﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Supermarket.Dal
{
    public class ProductDB
    {

        public static void CreateProduct(Product product)
        {
            DataClassesSupermarketDataContext db = new DataClassesSupermarketDataContext();
            product.InStock = 0;
            product.Enabled = false;
            db.Products.InsertOnSubmit(product);
            db.SubmitChanges();
        }

        public static List<object> GetAllProducts()
        {
            DataClassesSupermarketDataContext db = new DataClassesSupermarketDataContext();
            var products =
                from prod in db.Products
                select new
                {
                    prod.ProductId,
                    prod.ProductName,
                    prod.ProductPrice,
                    prod.Measure.MeasureCode,
                    prod.Company.CompanyName,
                    prod.Category.CategoryName,
                    prod.InStock
                };
            return products.ToList<object>();
        }

        public static Product GetItem(int id)
        {
            DataClassesSupermarketDataContext db = new DataClassesSupermarketDataContext();
            var product =
                from prod in db.Products
                where prod.ProductId == id
                select prod;
            if (product.Count() > 0)
            {
                return product.First();
            }

            return null;
        }

        public static void Update(Product product)
        {
            DataClassesSupermarketDataContext db = new DataClassesSupermarketDataContext();

            var productToUpdate =
                from prod in db.Products
                where prod.ProductId == product.ProductId
                select prod;
            productToUpdate.First().ProductName = product.ProductName;
            productToUpdate.First().ProductPrice = product.ProductPrice;
            productToUpdate.First().CompanyId = product.CompanyId;
            productToUpdate.First().CategoryId = product.CategoryId;
            productToUpdate.First().MeasureId = product.MeasureId;

            db.SubmitChanges();
        }

        public static List<object> GetAvailableProducts()
        {
            DataClassesSupermarketDataContext db = new DataClassesSupermarketDataContext();
            var products =
                from prod in db.Products
                where prod.InStock > 0
                select new
                {
                    prod.ProductId,
                    prod.ProductName,
                    prod.ProductPrice,
                    prod.Measure.MeasureCode,
                    prod.Company.CompanyName,
                    prod.Category.CategoryName,
                    prod.InStock
                };
            return products.ToList<object>();              
        }
    }
}
